package com.csii.adhoc.JdbcTemplate;

import cn.hutool.core.util.NumberUtil;
import cn.hutool.json.JSON;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONUtil;
import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.csii.adhoc.util.StringUtils;
import com.fasterxml.jackson.databind.util.JSONPObject;
import com.sun.org.apache.bcel.internal.generic.IF_ACMPEQ;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * T_DATA_FROM
 *
 * @description: 分页工具类
 * @author: panChuanJiang
 * @create: 2021-02-20 17:10
 */


@Slf4j
@Component
public class PageHelperUtils {
    @Autowired
    private JDBConfig jdbConfig;

    public static IPage<Map<String, Object>> getPageResult(String modelId,int pageNo, int pageSize, String sql, DBType dbType, JdbcTemplate jdbcTemplate) {
        int start = (pageNo - 1) * pageSize;

        String pageQuerySql = "";
        switch (dbType) {
            case MYSQL:
                sql = sql.replace("\"", "");
                pageQuerySql = getMysqlPageSql(sql, start, pageSize);
                break;
            case ORACLE:
            case GOLDILOCKS: //oracle分页和goldilocks相同
                pageQuerySql = getOraclePageSql(sql, start, pageSize);
                break;
        }
        String smartCountSql = getSimpleCountSql(sql,"0");
        Long total = jdbcTemplate.queryForObject(smartCountSql, Long.class);
        total = total == null ? 0 : total;
        log.info("执行的分页sql为:{}", pageQuerySql);
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(pageQuerySql);
        resHandle(dbType, maps);
        IPage<Map<String, Object>> res  = new Page<>();
        //  如果modelId不等于空时才做翻译
            if (!StringUtils.isEmpty(modelId) && NumberUtil.isNumber(modelId)) {
            String sqlTmp = "select json_data,COL_NAME from t_data_nickname_info where  model_id = '"+modelId+"'";
            List<Map<String, Object>> list = new PageHelperUtils().getJdbcTemplate().queryForList(sqlTmp);
            list.forEach(x -> {
                if (!StringUtils.isEmpty(x.get("JSON_DATA") == null ? null: x.get("JSON_DATA")+"")) {
                    Map<String, Object> objectMap = JSONUtil.parse(x.get("JSON_DATA")).toBean(Map.class);
                    String colName = x.get("COL_NAME") + "";
                    if (!StringUtils.isEmpty(colName)) {
                        maps.forEach(y -> {
                            for (String key : y.keySet()) {
                                if (colName.equals(key)) {
                                    for (String value : objectMap.keySet()) {
                                        if (value.equals(y.get(key))) {
                                            y.put(key, objectMap.get(value));
                                        }
                                    }
                                }
                            }
                        });
                    }
                }

            });

        }

        res.setRecords(maps)
                .setCurrent(pageNo)
                .setSize(pageSize)
                .setTotal(total);
        return res;
    }

    /**
     * 对结果集进行处理
     *
     * @param dbType 数据库类型
     * @param maps   结果
     */
    private static void resHandle(DBType dbType, List<Map<String, Object>> maps) {
        if ((DBType.ORACLE == dbType || DBType.GOLDILOCKS == dbType) && maps != null && maps.size() > 0) {
            maps.forEach(x -> x.remove("ROW_ID"));
        }
    }
    /**
     * 获取普通的Count-sql
     *
     * @param sql 原查询sql
     * @return 返回count查询sql
     */
    public static String getSimpleCountSql(final String sql, String name) {
        StringBuilder stringBuilder = new StringBuilder(sql.length() + 40);
        stringBuilder.append("select count(");
        stringBuilder.append(name);
        stringBuilder.append(") from (");
        stringBuilder.append(sql);
        stringBuilder.append(") tmp_count");
        return stringBuilder.toString();
    }

    private static String getMysqlPageSql(String sql, int start, int size) {
        StringBuilder sqlBuilder = new StringBuilder(sql);
        sqlBuilder.append(" limit ").append(start).append(",")
                .append(size);
        return sqlBuilder.toString();
    }

    private static String getOraclePageSql(String sql, int start, int size) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT * FROM ( ")
                .append(" SELECT TMP_PAGE.*, ROWNUM ROW_ID FROM ( ")
                .append(sql)
                .append(" ) TMP_PAGE WHERE ROWNUM <= ")
                .append(start + size)
                .append(" ) WHERE ROW_ID > ")
                .append(start);
        return sqlBuilder.toString();
    }
    /*
    public static void main(String[] args) throws SQLException {
        JdbcTemplateFactory factory = new JdbcTemplateFactory(1);
        DataSourceConfig config = new DataSourceConfig();
        //oracle
        config.setUrl("jdbc:oracle:thin:@192.168.20.38:1521:kfdb")
                .setUsername("pos").setPasswd("pos")
                .setDriverClass("oracle.jdbc.driver.OracleDriver")
                .setConnId("111");
        JdbcTemplate jdbcTemplate = factory.getBean(config);
        String sql = "select * from PCJ_ADHOC_TEST";
        PageVo<Map<String, Object>> pageResult = PageHelperUtils.getPageResult(2, 10, sql, DBType.ORACLE, jdbcTemplate);
        System.out.println(pageResult);
        //mysql
        config.setUrl("jdbc:mysql://127.0.0.1:3306/shiro?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai")
                .setUsername("root").setPasswd("root")
                .setDriverClass("com.mysql.cj.jdbc.Driver")
                .setConnId("111");
        JdbcTemplate jdbcTemplate = factory.getBean(config);
        String sql = "select * from permission";
        Page<Map<String, Object>> pageResult = PageHelperUtils.getPageResult(2, 3, sql, DBType.MYSQL, jdbcTemplate);
        System.out.println(pageResult);
        //GOLDILOCKS
        config.setUrl("jdbc:goldilocks://172.16.57.124:22581/test")
                .setUsername("dw").setPasswd("csii1234")
                .setDriverClass("sunje.goldilocks.jdbc.GoldilocksDriver")
                .setConnId("111");
        JdbcTemplate jdbcTemplate = factory.getBean(config);
        String sql = "select * from t_user_log";
        Page<Map<String, Object>> pageResult = PageHelperUtils.getPageResult(2, 3, sql, DBType.GOLDILOCKS, jdbcTemplate);
        System.out.println(pageResult);
    }*/


    private  JdbcTemplate getJdbcTemplate()  {
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(jdbConfig.JDBC_URL);
        dataSource.setDriverClassName(jdbConfig.JDBC_DRIVER_CLASS_NAME);
        dataSource.setUsername(jdbConfig.JDBC_USER_NAME);
        dataSource.setPassword(jdbConfig.JDBC_PASSWORD);
        try {
            dataSource.init();
        }catch (Exception e) {

        }
        jdbcTemplate.setDataSource(dataSource);
        return jdbcTemplate;
    }
}
